﻿Imports System.Data.SqlClient
Imports System.Linq
Public Class frmStudentSchedule
    Dim CN As New SqlConnection
    Dim StudentID As Integer
    Public Sub New(ByVal ID As Integer)
        Me.InitializeComponent()
        StudentID = ID
    End Sub

    Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click
        End
    End Sub

    Private Sub frmStudentSchedule_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim availability As Dictionary(Of Char, String)
        CN.ConnectionString = My.Settings.IT308ConnectionString
        availability = New Dictionary(Of Char, String)
        availability.Add(CChar("a"), "Monday Morning")
        availability.Add(CChar("b"), "Monday Afternoon")
        availability.Add(CChar("c"), "Monday Evening")
        availability.Add(CChar("d"), "Tuesday Morning")
        availability.Add(CChar("e"), "Tuesday Afternoon")
        availability.Add(CChar("f"), "Tuesday Evening")
        availability.Add(CChar("g"), "Wednesday Morning")
        availability.Add(CChar("h"), "Wednesday Afternoon")
        availability.Add(CChar("i"), "Wednesday Evening")
        availability.Add(CChar("j"), "Thursday Morning")
        availability.Add(CChar("k"), "Thursday Afternoon")
        availability.Add(CChar("l"), "Thursday Evening")
        availability.Add(CChar("m"), "Friday Morning")
        availability.Add(CChar("n"), "Friday Afternoon")
        availability.Add(CChar("o"), "Friday Evening")

        'WB - creates the connection to database
        Dim LI As New ListViewItem
        Dim cmd As New SqlCommand
        cmd.Connection = CN
        cmd.CommandText = "SELECT CLASSES.CoursesID, COURSES.Name, CLASSES.ClassTime, TEACHER.FName, TEACHER.LName, count(STUDENTSCLASSES.StudentsID) as StudentCount FROM CLASSES inner join TEACHER on CLASSES.TeacherID = TEACHER.ID inner join STUDENTSCLASSES on CLASSES.ID = STUDENTSCLASSES.ClassesID 	inner join COURSES on CLASSES.CoursesID = COURSES.ID WHERE STUDENTSCLASSES.StudentsID = @StudentsID GROUP BY CLASSES.COURSESID, COURSES.Name, CLASSES.ClassTime, TEACHER.LName, TEACHER.FName"
        Dim Reader As SqlDataReader = Nothing
        cmd.Parameters.AddWithValue("@StudentsID", StudentID)

        'WB - tries to open the connection, reports an error it it fails
        Try
            CN.Open()
        Catch ex As Exception
            MessageBox.Show("Could not connect to the Database - " & ex.Message, "Error")
        End Try

        'WB - clears all the items out of lsvCourse
        lsvCreatedClasses.Items.Clear()
        'lstStudents.Items.Clear()

        'WB - calls the procedure to generate the possibilites and places the courses in lsvCourses
        Try
            Reader = cmd.ExecuteReader
            While Reader.Read
                LI = New ListViewItem
                LI.Text = Convert.ToString(Reader.Item("CoursesID"))
                LI.SubItems.Add(Convert.ToString(Reader.Item("Name")))
                LI.SubItems.Add(availability(Convert.ToChar(Reader.Item("ClassTime"))))
                LI.SubItems.Add(Convert.ToString(Reader.Item("StudentCount")))
                LI.SubItems.Add(Convert.ToString(Reader.Item("FName")) & " " & Convert.ToString(Reader.Item("LName")))
                lsvCreatedClasses.Items.Add(LI)
            End While
            Reader.Close()
        Catch ex As Exception
            MessageBox.Show("Cannot Load Currently Scheduled Classes - " & ex.Message, "Error")
        Finally
            Try
                Reader.Close()
            Catch ex As Exception
            End Try
            If CN.State = ConnectionState.Open Then CN.Close()
        End Try
    End Sub
End Class